﻿/****************************************************************************
 * 模    块	：SQL语句条件帮助类
 * 作    者	：Jasper
 * 编写时间	：2009-3-10
 * 实现功能	：拼接SQL语句查询条件。
 * 详细描述	：
 ****************************************************************************/

namespace Basic4Net.Utility.Tools
{
	using System;
	using System.Collections.Generic;
	using System.Text;

	#region SQLHelperForQuery类
	public class SQLHelperForQuery
	{
		#region 内部变量
		private List<SQLHelperForQueryItem> Search = new List<SQLHelperForQueryItem>();
		private List<SQLHelperForQueryItem> Order = new List<SQLHelperForQueryItem>();

		/// <summary>
		/// 是否区分大小写
		/// </summary>
		private readonly bool CaseSensitive = false;
		#endregion

		#region 构造函数
		/// <summary>
		/// 创建SQLHelperForQuery类
		/// </summary>
		/// <param name="CaseSensitive">是否区分大小写，默认不区分</param>
		public SQLHelperForQuery(bool CaseSensitive)
		{
			this.CaseSensitive = CaseSensitive;
		}


		public SQLHelperForQuery() : this(false) { }
		#endregion

		#region AddCondition
		/// <summary>
		/// 增加查询条件
		/// </summary>
		/// <param name="_TableName">表名</param>
		/// <param name="_FieldName">字段名</param>
		/// <param name="_DataTypeUtils">数据类型</param>
		/// <param name="_Operator">对比方式</param>
		/// <param name="_Value">值</param>
		public void AddCondition(string _TableName, string _FieldName, EDataTypeUtils _DataTypeUtils, EOperator _Operator, string _Value)
		{
			Search.Add(new SQLHelperForQueryItem(_TableName, _FieldName, _DataTypeUtils, _Operator, this.CaseSensitive ? _Value : _Value.ToUpper()));
		}

		/// <summary>
		/// 增加查询条件
		/// </summary>
		/// <param name="_FieldName">字段名</param>
		/// <param name="_DataTypeUtils">数据类型</param>
		/// <param name="_Operator">对比方式</param>
		/// <param name="_Value">值</param>
		public void AddCondition(string _FieldName, EDataTypeUtils _DataTypeUtils, EOperator _Operator, string _Value)
		{
			AddCondition(String.Empty, _FieldName, _DataTypeUtils, _Operator, this.CaseSensitive ? _Value : _Value.ToUpper());
		}

		/// <summary>
		/// 增加查询条件
		/// </summary>
		/// <param name="_TableName">表名</param>
		/// <param name="_FieldName">字段名</param>
		/// <param name="_Operator">对比方式</param>
		/// <param name="_Value">日期</param>
		public void AddCondition(string _TableName, string _FieldName, EOperator _Operator, DateTime _Value)
		{
			AddCondition(_TableName, _FieldName, EDataTypeUtils.Date, _Operator, _Value.ToString("yyyy-MM-dd HH:mm:ss"));
		}

		/// <summary>
		/// 增加查询条件
		/// </summary>
		/// <param name="_FieldName">字段名</param>
		/// <param name="_Operator">对比方式</param>
		/// <param name="_Value">日期</param>
		public void AddCondition(string _FieldName, EOperator _Operator, DateTime _Value)
		{
			AddCondition(String.Empty, _FieldName, _Operator, _Value);
		}
		#endregion

		#region AddOrderCondition
		/// <summary>
		/// 增加排序条件
		/// </summary>
		/// <param name="_TableName">表名</param>
		/// <param name="_FieldName">字段名</param>
		/// <param name="_DataTypeUtils">数据类型</param>
		/// <param name="_Sort">排序方式</param>
		public void AddOrderCondition(string _TableName, string _FieldName, EDataTypeUtils _DataTypeUtils, SQLHelperForQuery.ESort _Sort)
		{
			Order.Add(new SQLHelperForQueryItem(_TableName, _FieldName, _DataTypeUtils, _Sort));
		}

		/// <summary>
		/// 增加排序条件
		/// </summary>
		/// <param name="_FieldName">字段名</param>
		/// <param name="_DataTypeUtils">数据类型</param>
		/// <param name="_Sort">排序方式</param>
		public void AddOrderCondition(string _FieldName, EDataTypeUtils _DataTypeUtils, ESort _Sort)
		{
			Order.Add(new SQLHelperForQueryItem(String.Empty, _FieldName, _DataTypeUtils, _Sort));
		}
		#endregion

		#region GetCondition
		/// <summary>
		/// 获取查询条件
		/// </summary>
		/// <returns></returns>
		public string GetCondition()
		{
			StringBuilder sb = new StringBuilder();
			for (int i = 0; i < Search.Count; i++)
				CreateConditionSQL(sb, Search[i]);

			if (Order.Count > 0)
			{
				sb.Append(" Order by ");
				for (int i = 0; i < Order.Count; i++)
				{
					CreateOrderConditionSQL(sb, Order[i]);
					if (i != Order.Count - 1)
						sb.Append(" , ");
				}
			}
			return sb.ToString();
		}
		#endregion

		#region 内部方法
		private void CreateOrderConditionSQL(StringBuilder _sb, SQLHelperForQueryItem _SQLHelperForQueryItem)
		{
			if (this.CaseSensitive)
				_sb.Append(String.Format(_SQLHelperForQueryItem.GetFormatString, " ", " "));
			else
				_sb.Append(String.Format(_SQLHelperForQueryItem.GetFormatString, "UPPER(", ")"));
		}

		private void CreateConditionSQL(StringBuilder _sb, SQLHelperForQueryItem _SQLHelperForQueryItem)
		{
			_sb.Append(" AND ");
			if (!this.CaseSensitive)
			{
				_sb.Append(" UPPER( ");
			}
			if (!String.IsNullOrEmpty(_SQLHelperForQueryItem._TableName))
				_sb.Append(_SQLHelperForQueryItem._TableName).Append(".");
			_sb.Append(_SQLHelperForQueryItem._FieldName).Append(" ");
			if (!this.CaseSensitive)
			{
				_sb.Append(") ");
			}

			_sb.Append(String.Format(_SQLHelperForQueryItem.GetFormatString, _SQLHelperForQueryItem._Value));
		}
		#endregion

		#region 枚举
		/// <summary>
		/// 数据类型
		/// </summary>
		public enum EDataTypeUtils
		{
			/// <summary>
			/// 文本
			/// </summary>
			String,
			/// <summary>
			/// 日期
			/// </summary>
			Date,
			/// <summary>
			/// 布朗值
			/// </summary>
			Bool,
			/// <summary>
			/// 数字类型
			/// </summary>
			Number
		};

		/// <summary>
		/// 对比条件
		/// </summary>
		public enum EOperator
		{
			/// <summary>
			/// 等于=
			/// </summary>
			Equal,
			/// <summary>
			/// 不等于!=
			/// </summary>
			NotEqual,
			/// <summary>
			/// 大于>
			/// </summary>
			GreaterThan,
			/// <summary>
			/// 大于等于>=
			/// </summary>
			GreaterThanEqual,
			/// <summary>
			/// 小于
			/// </summary>
			LessThan,
			/// <summary>
			/// 小于等于
			/// </summary>
			LessThanEqual,
			/// <summary>
			/// in，对EDataTypeUtils.Date和EDataTypeUtils.Bool无效
			/// </summary>
			In,
			/// <summary>
			/// not in，对EDataTypeUtils.Date和EDataTypeUtils.Bool无效
			/// </summary>
			NotIn,
			/// <summary>
			/// like 'ABC%'，只对EDataTypeUtils.String有效
			/// </summary>
			Like,
			/// <summary>
			/// like 'ABC%'，只对EDataTypeUtils.String有效
			/// </summary>
			LikeLeft,
			/// <summary>
			/// like '%ABC'，只对EDataTypeUtils.String有效
			/// </summary>
			LikeRight,
			/// <summary>
			/// 替换空格like '%AB%C%'，只对EDataTypeUtils.String有效
			/// </summary>
			LikeReplaceSpace
		};

		/// <summary>
		/// 排序方式
		/// </summary>
		public enum ESort
		{
			/// <summary>
			/// 升序
			/// </summary>
			ASC,
			/// <summary>
			/// 降序
			/// </summary>
			DESC
		};
		#endregion
	}
	#endregion

	#region SQLHelperForQueryItem类
	internal class SQLHelperForQueryItem
	{
		internal readonly string _TableName;
		internal readonly string _FieldName;
		internal readonly SQLHelperForQuery.EOperator? _Operator;
		internal readonly SQLHelperForQuery.ESort? _Sort;
		internal readonly string _Value;
		internal readonly SQLHelperForQuery.EDataTypeUtils _DataTypeUtils;
		private string FormatString = null;

		internal SQLHelperForQueryItem(string _TableName, string _FieldName, SQLHelperForQuery.EDataTypeUtils _DataTypeUtils, SQLHelperForQuery.EOperator _Operator, string _Value)
		{
			this._TableName = _TableName;
			this._FieldName = _FieldName;
			this._Operator = _Operator;
			this._DataTypeUtils = _DataTypeUtils;

			if (this._DataTypeUtils == SQLHelperForQuery.EDataTypeUtils.String && (this._Operator == SQLHelperForQuery.EOperator.In || this._Operator == SQLHelperForQuery.EOperator.NotIn))
				this._Value = _Value.Replace("'", "''").Replace(",", "','");
			else if (this._DataTypeUtils == SQLHelperForQuery.EDataTypeUtils.String && this._Operator == SQLHelperForQuery.EOperator.LikeReplaceSpace)
				this._Value = _Value.Replace("'", "''").Replace(' ', '%');
			else
				this._Value = _Value.Replace("'", "''");
		}

		internal SQLHelperForQueryItem(string _TableName, string _FieldName, SQLHelperForQuery.EDataTypeUtils _DataTypeUtils, SQLHelperForQuery.ESort _Sort)
		{
			this._TableName = _TableName;
			this._FieldName = _FieldName;
			this._Sort = _Sort;
			this._DataTypeUtils = _DataTypeUtils;
		}

		internal string GetFormatString
		{
			get
			{
				if (this.FormatString == null)
				{
					if (this._Operator != null)
						switch (_DataTypeUtils)
						{
							case SQLHelperForQuery.EDataTypeUtils.String:
								#region EDataTypeUtils.String
								switch (_Operator)
								{
									case SQLHelperForQuery.EOperator.Equal:
										this.FormatString = " <> '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.GreaterThan:
										this.FormatString = " > '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.GreaterThanEqual:
										this.FormatString = " >= '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.LessThan:
										this.FormatString = " < '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.LessThanEqual:
										this.FormatString = " <= '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.In:
										this.FormatString = " in ('{0}') ";
										break;
									case SQLHelperForQuery.EOperator.NotIn:
										this.FormatString = " not in ('{0}') ";
										break;
									case SQLHelperForQuery.EOperator.Like:
										this.FormatString = " like '%{0}%' ";
										break;
									case SQLHelperForQuery.EOperator.LikeLeft:
										this.FormatString = " like '{0}%' ";
										break;
									case SQLHelperForQuery.EOperator.LikeRight:
										this.FormatString = " like '%{0}' ";
										break;
									case SQLHelperForQuery.EOperator.LikeReplaceSpace:
										this.FormatString = " like '%{0}%' ";
										break;
									default:
										this.FormatString = String.Empty;
										break;
								}
								#endregion
								break;
							case SQLHelperForQuery.EDataTypeUtils.Date:
								#region EDataTypeUtils.Date
								switch (_Operator)
								{
									case SQLHelperForQuery.EOperator.Equal:
										this.FormatString = " =  cast('{0}' as datetime) ";
										break;
									case SQLHelperForQuery.EOperator.NotEqual:
										this.FormatString = " <> cast('{0}' as datetime) ";
										break;
									case SQLHelperForQuery.EOperator.GreaterThan:
										this.FormatString = " > cast('{0}' as datetime) ";
										break;
									case SQLHelperForQuery.EOperator.GreaterThanEqual:
										this.FormatString = " >= cast('{0}' as datetime) ";
										break;
									case SQLHelperForQuery.EOperator.LessThan:
										this.FormatString = " < cast('{0}' as datetime) ";
										break;
									case SQLHelperForQuery.EOperator.LessThanEqual:
										this.FormatString = " <= cast('{0}' as datetime) ";
										break;
									case SQLHelperForQuery.EOperator.In:
									case SQLHelperForQuery.EOperator.NotIn:
									case SQLHelperForQuery.EOperator.Like:
									case SQLHelperForQuery.EOperator.LikeLeft:
									case SQLHelperForQuery.EOperator.LikeRight:
									case SQLHelperForQuery.EOperator.LikeReplaceSpace:
									default:
										this.FormatString = String.Empty;
										break;
								}
								#endregion
								break;
							case SQLHelperForQuery.EDataTypeUtils.Bool:
								#region EDataTypeUtils.Bool
								switch (_Operator)
								{
									case SQLHelperForQuery.EOperator.Equal:
										this.FormatString = " = '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.NotEqual:
										this.FormatString = " <> '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.GreaterThan:
										this.FormatString = " > '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.GreaterThanEqual:
										this.FormatString = " >= '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.LessThan:
										this.FormatString = " < '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.LessThanEqual:
										this.FormatString = " <= '{0}' ";
										break;
									case SQLHelperForQuery.EOperator.In:
									case SQLHelperForQuery.EOperator.NotIn:
									case SQLHelperForQuery.EOperator.Like:
									case SQLHelperForQuery.EOperator.LikeLeft:
									case SQLHelperForQuery.EOperator.LikeRight:
									case SQLHelperForQuery.EOperator.LikeReplaceSpace:
									default:
										this.FormatString = String.Empty;
										break;
								}
								#endregion
								break;
							case SQLHelperForQuery.EDataTypeUtils.Number:
								#region EDataTypeUtils.Number
								switch (_Operator)
								{
									case SQLHelperForQuery.EOperator.Equal:
										this.FormatString = " = {0} ";
										break;
									case SQLHelperForQuery.EOperator.NotEqual:
										this.FormatString = " <> {0} ";
										break;
									case SQLHelperForQuery.EOperator.GreaterThan:
										this.FormatString = " > {0} ";
										break;
									case SQLHelperForQuery.EOperator.GreaterThanEqual:
										this.FormatString = " >= {0} ";
										break;
									case SQLHelperForQuery.EOperator.LessThan:
										this.FormatString = " < {0} ";
										break;
									case SQLHelperForQuery.EOperator.LessThanEqual:
										this.FormatString = " <= {0} ";
										break;
									case SQLHelperForQuery.EOperator.In:
										this.FormatString = " in ({0}) ";
										break;
									case SQLHelperForQuery.EOperator.NotIn:
										this.FormatString = " not in ({0}) ";
										break;
									case SQLHelperForQuery.EOperator.Like:
									case SQLHelperForQuery.EOperator.LikeLeft:
									case SQLHelperForQuery.EOperator.LikeRight:
									case SQLHelperForQuery.EOperator.LikeReplaceSpace:
									default:
										this.FormatString = String.Empty;
										break;
								}
								#endregion
								break;
							default:
								this.FormatString = String.Empty;
								break;
						}
					else
						if (_DataTypeUtils == SQLHelperForQuery.EDataTypeUtils.String)
						{
							this.FormatString = " {0} ";
							if (!String.IsNullOrEmpty(this._TableName))
								this.FormatString += this._TableName + ".";
							this.FormatString += this._FieldName;
							this.FormatString += " {1} ";
							this.FormatString += this._Sort.ToString();
						}
						else
						{
							if (!String.IsNullOrEmpty(this._TableName))
								this.FormatString += this._TableName + ".";
							this.FormatString += this._FieldName;
							this.FormatString += " ";
							this.FormatString += this._Sort.ToString();
						}
				}
				return this.FormatString;
			}
		}
	}
	#endregion
}
